﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using DBManager.UserControls;
using System.Text;
using DBManager.Service;

namespace DBManager {
    public partial class Form1 : Form {

        SqlConnection conn = new SqlConnection();
        SqlCommand cmd = new SqlCommand();

        public Form1() {
            InitializeComponent();
            conn.ConnectionString = @"server=.\MyServer;database=FamilyCooperation;uid=sa;pwd=sa";
            conn.Open();
            cmd.Connection = conn;
        }
        //private Dictionary<string, string> colContainer = new Dictionary<string, string>();
        List<ucColumn> lstAddControl = new List<ucColumn>(); // used for create sql
        private int countOfAdd = 0; // used to gerate a ID for record the TextBox and button

        private void Form1_Load(object sender, EventArgs e) {
            GetDBTablesInfo();

            this.rdoAdd.Checked = true;
            this.lstTbl.Enabled = false;
        }

        private void GetDBTablesInfo() {
            this.lstTbl.Items.Clear();
            SqlDataReader reader = this.GetReader("table", "");
            while (reader.Read()) {
                string tableName = reader.GetString(2);
                this.lstTbl.Items.Add(tableName);
            }
            reader.Close();
        }

        private SqlDataReader GetReader(string pType, string pTableName) {
            string sp = string.Empty;
            if (pType == "table") {
                sp = "exec sp_tables @table_type='''TABLE'''";
            }
            else {
                sp = string.Format("exec sp_columns '{0}'", pTableName);
            }

            SqlCommand comd = new SqlCommand();
            comd.Connection = conn;
            comd.CommandText = sp;

            return comd.ExecuteReader();
        }

        /// <summary>
        /// Update table
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void lstTbl_DoubleClick(object sender, EventArgs e) {
            ListBox lb = (ListBox)sender;
            string tableName = lb.SelectedItem as string;
            FillTheColumnsInfo(tableName);
        }

        private void FillTheColumnsInfo(string pTableName) {
            ClearColumnsGenerated();
            this.txtTableName.Text = pTableName;
            this.countOfAdd = 0;
            SqlDataReader reader = this.GetReader("column", pTableName);
            while (reader.Read()) {
                string columnName = reader.GetString(3);
                string columnType = reader.GetString(5);
                int precision = reader.GetInt32(6);
                if (columnName == "ID") {
                    continue; // skip the ID column;
                }
                if (columnName == "CreateOn" || columnName == "CreateBy" || columnName == "CreateByName" ||
                    columnName == "UpdateOn" || columnName == "UpdateBy" || columnName == "UpdateByName") {
                    continue;
                }

                this.CreateNewColumn(columnName, columnType, precision, false);
            }

            reader.Close();
        }

        private void ClearColumnsGenerated() {
            // clear the columns generated
            foreach (Control item in this.lstAddControl) {
                this.Controls.Remove(item);
            }
            this.lstAddControl.Clear();
            this.countOfAdd = 0;
        }

        private void btnAdd_Click(object sender, EventArgs e) {
            CreateNewColumn();
        }

        private void CreateNewColumn() {
            this.CreateNewColumn("", "", 0, true);
        }

        private void CreateNewColumn(string pColumnName, string pColumnType, int pPercision, bool pIsNew) {
            ucColumn uc = new ucColumn();
            uc.Location = new System.Drawing.Point(12, 157 + countOfAdd * 27);
            uc.ColumnName = pColumnName;
            uc.ColumnType = pColumnType;
            uc.Percision = pPercision;
            uc.ModifyEnable = !pIsNew;
            uc.Delete += this.btnDel_Click;
            uc.Status = ColumnStaus.New;
            this.Controls.Add(uc);
            this.lstAddControl.Add(uc);
            if (!pIsNew) {
                uc.OldColumnName = pColumnName;
                uc.Status = ColumnStaus.UnChange;
            }
            else {
                uc.Status = ColumnStaus.New;
            }
            this.countOfAdd++;
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e) {
            this.conn.Close();
        }

        private void button1_Click(object sender, EventArgs e) {
            string tableName = this.txtTableName.Text;
            if (rdoAdd.Checked) {
                string createTableSql = SqlGenerator.SqlForCreateTable(this.lstAddControl, tableName);
                ExeSql(createTableSql);
                // re-access DB to refresh list at the right-panel
                this.GetDBTablesInfo();
                rdoModify.Checked = true;
                FillTheColumnsInfo(tableName);
                string spSQL = SqlGenerator.GetSPString(this.lstAddControl, tableName);
                FrmShowSP sp = new FrmShowSP();
                sp.SetSP(spSQL);
                
                sp.ShowDialog();
            }
            else {
                StringBuilder sb = new System.Text.StringBuilder();
                StringBuilder sbInsert = new System.Text.StringBuilder();
                StringBuilder sbUpdate = new System.Text.StringBuilder();
                StringBuilder sbDelete = new System.Text.StringBuilder();

                foreach (ucColumn item in this.lstAddControl) {
                    switch (item.Status) { 
                        case ColumnStaus.New:
                            sbInsert.AppendFormat("alter table {0} add {1} {2}", tableName, item.ColumnName, item.FullColumnType).AppendLine();
                            break;
                        case ColumnStaus.UnChange:
                            break;
                        case ColumnStaus.Update:
                            if (item.OldColumnName != item.ColumnName) {
                                sbUpdate.AppendFormat("exec sp_rename '{0}.{1}','{2}','column' go", tableName, item.OldColumnName, item.ColumnName).AppendLine();
                            }
                            sbUpdate.AppendFormat("alter table {0} alter column {1} {2}", tableName, item.ColumnName, item.FullColumnType).AppendLine();
                            break;
                        case ColumnStaus.Delete:
                            sbDelete.AppendFormat("alter table {0} drop column {1}", tableName, item.ColumnName).AppendLine();
                            break;
                    }
                }
                sb.AppendLine(sbInsert.ToString()).AppendLine(sbUpdate.ToString()).AppendLine(sbDelete.ToString());
                ExeSql(sb.ToString());
                this.ClearColumnsGenerated();
                this.FillTheColumnsInfo(tableName);
            }
        }

        private void ExeSql(string pSql) {
            this.cmd.CommandType = CommandType.Text;
            this.cmd.CommandText = pSql;
            this.cmd.ExecuteNonQuery();
            MessageBox.Show("好的，执行完了！");
        }

        private void btnDel_Click(object sender, EventArgs e) {
            Button btn = sender as Button;
            if (btn != null) {
                ucColumn uc = btn.Parent as ucColumn;
                if (uc.Status == ColumnStaus.New) {
                    this.lstAddControl.Remove(uc); // new item, delete will not create sql
                }
                else {
                    uc.Status = ColumnStaus.Delete;
                }

                this.Controls.Remove(uc);
            }
        }

        private void rdoModify_CheckedChanged(object sender, EventArgs e) {
            ClearColumnsGenerated();
            this.lstTbl.Enabled = this.rdoModify.Checked;
            this.txtTableName.ReadOnly = this.rdoModify.Checked;
            this.txtTableName.Text = string.Empty;
            if (this.rdoModify.Checked) {
                this.GetDBTablesInfo();
            }
        }
    }
}
